#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库初始化脚本
用于创建数据库、表结构并插入测试数据
"""

import os
import sys
from datetime import datetime, timedelta
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from faker import Faker
import random

# 添加项目根目录到Python路径
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from database.connection import create_database, DATABASE_URL, engine
from database.models import Base, User, UploadFile
from utils.auth import get_password_hash

# 初始化Faker
fake = Faker('zh_CN')

def create_mysql_database():
    """
    创建MySQL数据库
    """
    print("正在创建MySQL数据库...")
    
    try:
        # 先连接到MySQL服务器（不指定数据库）
        server_url = DATABASE_URL.replace('/aigd100', '')
        server_engine = create_engine(server_url)
        
        # 创建数据库
        with server_engine.connect() as conn:
            conn.execute(text("CREATE DATABASE IF NOT EXISTS aigd100 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"))
        
        print("✅ 数据库 'aigd100' 创建成功")
    except Exception as e:
        print(f"❌ 创建数据库失败: {str(e)}")
        return False
    
    return True

def create_tables():
    """
    创建数据表
    """
    print("正在创建数据表...")
    
    try:
        # 创建所有表
        Base.metadata.create_all(bind=engine)
        print("✅ 数据表创建成功")
        
        return engine
    except Exception as e:
        print(f"❌ 创建数据表失败: {str(e)}")
        return None

def insert_test_users(engine):
    """
    插入10条测试用户数据
    """
    print("正在插入测试用户数据...")
    
    try:
        # 创建会话
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        db = SessionLocal()
        
        # 生成10个测试用户
        test_users = []
        
        for i in range(10):
            # 生成随机用户数据
            username = f"user{i+1:02d}"
            password = get_password_hash("123456")  # 统一密码为123456
            nickname = fake.name()
            gender = random.choice([1, 2, 3])  # 1-男, 2-女, 3-保密
            age = random.randint(18, 60)
            country = "中国"
            province = fake.province()
            city = fake.city()
            district = fake.district()
            address = fake.address()
            role = random.choice([1, 2, 3, 4])  # 1-普通用户 2-管理员 3-评委 4-超级管理员
            phone = fake.phone_number()
            
            # 随机生成社交数据
            like_num = random.randint(0, 1000)
            comment_num = random.randint(0, 500)
            follow_num = random.randint(0, 200)
            fans_num = random.randint(0, 300)
            video_num = random.randint(0, 50)
            collect_num = random.randint(0, 100)
            
            # 创建时间（过去30天内的随机时间）
            create_time = fake.date_time_between(start_date='-30d', end_date='now')
            
            user = User(
                username=username,
                password=password,
                nickname=nickname,
                avatar=f"https://api.dicebear.com/7.x/avataaars/svg?seed={username}",  # 使用随机头像API
                gender=gender,
                age=age,
                country=country,
                province=province,
                city=city,
                district=district,
                address=address,
                role=role,
                phone=phone,
                like_num=like_num,
                comment_num=comment_num,
                follow_num=follow_num,
                fans_num=fans_num,
                video_num=video_num,
                collect_num=collect_num,
                create_time=create_time,
                update_time=create_time,
                is_delete=False
            )
            
            test_users.append(user)
        
        # 批量插入用户
        db.add_all(test_users)
        db.commit()
        
        print("✅ 成功插入10条测试用户数据")
        print("\n测试用户信息:")
        print("-" * 80)
        print(f"{'用户名':<12} {'昵称':<15} {'角色':<8} {'手机号':<15} {'城市':<10}")
        print("-" * 80)
        
        for user in test_users:
            print(f"{user.username:<12} {user.nickname:<15} {user.role:<8} {user.phone:<15} {user.city:<10}")
        
        print("-" * 80)
        print("💡 所有测试用户的密码都是: 123456")
        
        db.close()
        return True
        
    except Exception as e:
        print(f"❌ 插入测试数据失败: {str(e)}")
        if 'db' in locals():
            db.rollback()
            db.close()
        return False

def insert_test_files(engine):
    """
    插入一些测试文件记录
    """
    print("正在插入测试文件记录...")
    
    try:
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        db = SessionLocal()
        
        # 获取前5个用户ID
        users = db.query(User).limit(5).all()
        
        test_files = []
        
        for i, user in enumerate(users):
            # 为每个用户创建1-2个文件记录
            file_count = random.randint(1, 2)
            
            for j in range(file_count):
                file_type = random.choice(["avatar", "file"])
                
                if file_type == "avatar":
                    original_name = f"avatar_{i+1}_{j+1}.jpg"
                    file_name = f"{fake.uuid4()}.jpg"
                    file_path = f"uploads/avatars/{file_name}"
                    mime_type = "image/jpeg"
                else:
                    original_name = f"document_{i+1}_{j+1}.pdf"
                    file_name = f"{fake.uuid4()}.pdf"
                    file_path = f"uploads/files/{file_name}"
                    mime_type = "application/pdf"
                
                upload_file = UploadFile(
                    user_id=user.id,
                    original_name=original_name,
                    file_name=file_name,
                    file_path=file_path,
                    file_size=random.randint(1024, 1024*1024*5),  # 1KB - 5MB
                    file_type=file_type,
                    mime_type=mime_type,
                    upload_time=fake.date_time_between(start_date='-15d', end_date='now'),
                    is_delete=False
                )
                
                test_files.append(upload_file)
        
        # 批量插入文件记录
        db.add_all(test_files)
        db.commit()
        
        print(f"✅ 成功插入{len(test_files)}条测试文件记录")
        
        db.close()
        return True
        
    except Exception as e:
        print(f"❌ 插入测试文件记录失败: {str(e)}")
        if 'db' in locals():
            db.rollback()
            db.close()
        return False

def main():
    """
    主函数：执行数据库初始化流程
    """
    print("🚀 开始初始化数据库...")
    print("=" * 60)
    
    # 1. 创建数据库
    if not create_mysql_database():
        print("❌ 数据库初始化失败")
        return
    
    # 2. 创建表结构
    engine = create_tables()
    if not engine:
        print("❌ 数据库初始化失败")
        return
    
    # 3. 插入测试用户数据
    if not insert_test_users(engine):
        print("❌ 数据库初始化失败")
        return
    
    # 4. 插入测试文件记录
    if not insert_test_files(engine):
        print("❌ 数据库初始化失败")
        return
    
    print("=" * 60)
    print("🎉 数据库初始化完成！")
    print("\n📋 初始化内容:")
    print("   ✅ 创建数据库 'aigd100'")
    print("   ✅ 创建用户表 (users)")
    print("   ✅ 创建文件表 (upload_files)")
    print("   ✅ 插入10条测试用户数据")
    print("   ✅ 插入测试文件记录")
    print("\n🔧 下一步:")
    print("   1. 配置环境变量 (.env 文件)")
    print("   2. 安装依赖: pip install -r requirements.txt")
    print("   3. 启动服务: python main.py")
    print("   4. 访问API文档: http://localhost:8000/docs")

if __name__ == "__main__":
    main()